package com.demo.excel;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @ClassName ImportExcel
 * @Description 导入excel
 * @Author zqc
 * @Date 2021/4/1 10:32
 * @Version 1.0
 */
@Slf4j
public class ExcelImport {

    /**
     * 从excel中读内容
     *
     * @param file       excel
     * @param sheetIndex 第几个sheet页
     * @param rownum     从第几行开始
     * @param titles     表头对应的字段
     * @return
     */
    public static List<Map<String, String>> readExcel(MultipartFile file, int sheetIndex, int rownum, String[] titles) {
        List<Map<String, String>> dataList = new ArrayList<>();
        Workbook wb = ExcelImport.createWorkBook(file);
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(sheetIndex);
            int maxRownum = sheet.getPhysicalNumberOfRows(); //总行数
            for (int i = rownum; i < maxRownum; i++) {
                Map<String, String> map = new HashMap<>();
                Row firstRow = sheet.getRow(i);
                for (int j = 0; j < titles.length; j++) {
                    String cellData = (String) ExcelImport.getCellFormatValue(firstRow.getCell(j), titles[j]);
                    mapSetValue(titles[j], cellData, map);
                }
                dataList.add(map);
            }
        }
        return dataList;
    }


    private static void mapSetValue(String title, String value, Map<String, String> map) {
        // 如果表头带有 -- 则说名这一列属于日期格式 --后面则是需要的日期格式 如 title--yyyy-mm-dd
        if (title.contains("--") && StringUtils.isNotBlank(value)) {
            String dateformat = title.substring(title.lastIndexOf("--") + 2);
            SimpleDateFormat dateDayEn = new SimpleDateFormat(dateformat);
            try {
                dateDayEn.parse(value);
            } catch (ParseException e) {
                log.error("import excel date fail  dataValue:{}", value, e);
                throw new RuntimeException("excel所导入的日期必须是: " + dateformat + " 格式");
        }
            map.put(title.substring(0, title.lastIndexOf("--")), value);
        } else {
            map.put(title, value);
        }
    }


    /**
     * 区别 .xls .xlsx
     *
     * @param file
     * @return
     */
    private static Workbook createWorkBook(MultipartFile file) {
        if (file == null) return null;
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        String extString = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (".xls".equals(extString)) {
                return new HSSFWorkbook(file.getInputStream());
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(file.getInputStream());
            }
            throw new RuntimeException("导入文件不是excel格式文件");
        } catch (FileNotFoundException e) {
            log.error("FileNotFoundException:", e);
        } catch (IOException e) {
            log.error("import excel fail:", e);
        }
        return wb;
    }

    /**
     * 将字段转为相应的格式
     *
     * @param cell
     * @return
     */
    private static Object getCellFormatValue(Cell cell, String title) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: { // 数字类型
                    if (title.contains("--")) {
                        Date date = cell.getDateCellValue();
                        String dateformat = title.substring(title.lastIndexOf("--") + 2);
                        SimpleDateFormat dateDayEn = new SimpleDateFormat(dateformat);
                        cellValue = dateDayEn.format(date);
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: { // 公式类型
                    cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                    break;
                }
                case Cell.CELL_TYPE_STRING: { //字符串类型
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    public static void main(String[] args) {
        String a = "dfsd--yyyy-mm-dd";
        System.out.println(a.substring(0, a.lastIndexOf("--")));
    }

}

